#!/usr/bin/env python3
"""
SkyWalking 数据库查询工具
直接查询 SQLite 数据库，显示采集的数据
"""
import os
import sys
from pathlib import Path
from datetime import datetime, timedelta, timezone

# 添加父目录到 Python 路径
sys.path.insert(0, str(Path(__file__).parent.parent))

from dotenv import load_dotenv
from database.schema import Service, ServiceMetric, Endpoint, EndpointMetric, Trace, CollectionLog
from sqlalchemy import create_engine, func, desc
from sqlalchemy.orm import sessionmaker

# 加载配置
env_path = Path(__file__).parent.parent / "config.env"
if env_path.exists():
    load_dotenv(env_path)

# 构建绝对路径
db_path = Path(__file__).parent.parent / "data" / "skywalking_data.db"
DATABASE_URL = f'sqlite:///{db_path}'

# 创建引擎和会话
engine = create_engine(DATABASE_URL, connect_args={'check_same_thread': False})
SessionLocal = sessionmaker(bind=engine)


def format_utc8(dt):
    """将 UTC 时间转换为 UTC+8 显示"""
    if dt is None:
        return "N/A"
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo=timezone.utc)
    utc8_time = dt.astimezone(timezone(timedelta(hours=8)))
    return utc8_time.strftime('%Y-%m-%d %H:%M:%S')


def query_services(limit=10):
    """查询服务列表"""
    print("\n" + "=" * 80)
    print("📦 服务列表")
    print("=" * 80)
    
    session = SessionLocal()
    try:
        services = session.query(Service).order_by(desc(Service.collected_at)).limit(limit).all()
        
        if not services:
            print("⚠️  没有服务数据")
            return
        
        print(f"\n最近 {len(services)} 个服务:")
        for i, s in enumerate(services, 1):
            print(f"\n{i}. {s.name}")
            print(f"   ID: {s.service_id}")
            print(f"   Layer: {s.layer or 'N/A'}")
            print(f"   采集时间: {format_utc8(s.collected_at)} (UTC+8)")
    finally:
        session.close()


def query_service_metrics(service_name=None, limit=20):
    """查询服务指标"""
    print("\n" + "=" * 80)
    print("📊 服务指标")
    print("=" * 80)
    
    session = SessionLocal()
    try:
        query = session.query(
            Service.name,
            ServiceMetric.metric_name,
            func.count(ServiceMetric.id).label('count'),
            func.min(ServiceMetric.timestamp).label('earliest'),
            func.max(ServiceMetric.timestamp).label('latest'),
            func.avg(ServiceMetric.value).label('avg_value')
        ).join(Service, ServiceMetric.service_id == Service.id)
        
        if service_name:
            query = query.filter(Service.name.like(f'%{service_name}%'))
        
        results = query.group_by(Service.name, ServiceMetric.metric_name).order_by(
            desc('latest')
        ).limit(limit).all()
        
        if not results:
            print("⚠️  没有服务指标数据")
            if service_name:
                print(f"   (筛选条件: 服务名包含 '{service_name}')")
            return
        
        print(f"\n找到 {len(results)} 条指标记录:")
        for r in results:
            print(f"\n服务: {r.name}")
            print(f"  指标: {r.metric_name}")
            print(f"  数据点数: {r.count}")
            print(f"  平均值: {r.avg_value:.2f}")
            print(f"  时间范围: {format_utc8(r.earliest)} ~ {format_utc8(r.latest)} (UTC+8)")
    finally:
        session.close()


def query_endpoints(service_name=None, limit=20):
    """查询端点"""
    print("\n" + "=" * 80)
    print("🔗 端点列表")
    print("=" * 80)
    
    session = SessionLocal()
    try:
        query = session.query(
            Service.name,
            func.count(Endpoint.id).label('endpoint_count')
        ).join(Endpoint, Service.id == Endpoint.service_id)
        
        if service_name:
            query = query.filter(Service.name.like(f'%{service_name}%'))
        
        results = query.group_by(Service.name).order_by(desc('endpoint_count')).limit(limit).all()
        
        if not results:
            print("⚠️  没有端点数据")
            return
        
        print(f"\n找到 {len(results)} 个服务的端点:")
        for r in results:
            print(f"\n{r.name}: {r.endpoint_count} 个端点")
    finally:
        session.close()


def query_traces(service_name=None, limit=20):
    """查询 Trace"""
    print("\n" + "=" * 80)
    print("🔍 Trace 列表")
    print("=" * 80)
    
    session = SessionLocal()
    try:
        query = session.query(
            Service.name,
            func.count(Trace.id).label('trace_count'),
            func.count(func.nullif(Trace.is_error, False)).label('error_count'),
            func.min(Trace.start_time).label('earliest'),
            func.max(Trace.start_time).label('latest')
        ).join(Service, Trace.service_id == Service.id)
        
        if service_name:
            query = query.filter(Service.name.like(f'%{service_name}%'))
        
        results = query.group_by(Service.name).order_by(desc('trace_count')).limit(limit).all()
        
        if not results:
            print("⚠️  没有 Trace 数据")
            return
        
        print(f"\n找到 {len(results)} 个服务的 Trace:")
        for r in results:
            error_rate = (r.error_count / r.trace_count * 100) if r.trace_count > 0 else 0
            print(f"\n{r.name}:")
            print(f"  总数: {r.trace_count}")
            print(f"  错误数: {r.error_count} ({error_rate:.2f}%)")
            print(f"  时间范围: {format_utc8(r.earliest)} ~ {format_utc8(r.latest)} (UTC+8)")
    finally:
        session.close()


def query_latest_metrics(service_name, limit=10):
    """查询某个服务的最新指标数据"""
    print("\n" + "=" * 80)
    print(f"📈 服务 '{service_name}' 的最新指标")
    print("=" * 80)
    
    session = SessionLocal()
    try:
        service = session.query(Service).filter(Service.name.like(f'%{service_name}%')).first()
        
        if not service:
            print(f"⚠️  找不到服务: {service_name}")
            return
        
        metrics = session.query(ServiceMetric).filter(
            ServiceMetric.service_id == service.id
        ).order_by(desc(ServiceMetric.timestamp)).limit(limit).all()
        
        if not metrics:
            print(f"⚠️  服务 '{service.name}' 没有指标数据")
            return
        
        print(f"\n最近 {len(metrics)} 条指标:")
        for m in metrics:
            print(f"\n{m.metric_name}")
            print(f"  值: {m.value}")
            print(f"  时间: {format_utc8(m.timestamp)} (UTC+8)")
    finally:
        session.close()


def main():
    """主函数"""
    import argparse
    
    parser = argparse.ArgumentParser(description='查询 SkyWalking 数据库内容')
    parser.add_argument('--services', action='store_true', help='查询服务列表')
    parser.add_argument('--metrics', action='store_true', help='查询服务指标汇总')
    parser.add_argument('--endpoints', action='store_true', help='查询端点列表')
    parser.add_argument('--traces', action='store_true', help='查询 Trace 列表')
    parser.add_argument('--service', type=str, help='指定服务名称（支持模糊匹配）')
    parser.add_argument('--limit', type=int, default=20, help='限制结果数量（默认20）')
    parser.add_argument('--latest-metrics', action='store_true', help='查询服务的最新指标数据（需配合 --service）')
    parser.add_argument('--all', action='store_true', help='查询所有类型的数据')
    
    args = parser.parse_args()
    
    # 如果没有指定任何参数，显示帮助
    if not any([args.services, args.metrics, args.endpoints, args.traces, args.latest_metrics, args.all]):
        parser.print_help()
        print("\n示例:")
        print("  python3 query_database.py --all                           # 查询所有数据")
        print("  python3 query_database.py --services                      # 查询服务列表")
        print("  python3 query_database.py --metrics --service basin      # 查询包含'basin'的服务的指标")
        print("  python3 query_database.py --latest-metrics --service basin  # 查询最新指标")
        print("  python3 query_database.py --traces --service basin --limit 5  # 限制5条结果")
        return
    
    print("=" * 80)
    print("🗄️  SkyWalking 数据库查询工具")
    print("=" * 80)
    print(f"\n数据库: {DATABASE_URL}")
    
    # 根据参数执行查询
    if args.all:
        query_services(args.limit)
        query_service_metrics(args.service, args.limit)
        query_endpoints(args.service, args.limit)
        query_traces(args.service, args.limit)
    else:
        if args.services:
            query_services(args.limit)
        
        if args.metrics:
            query_service_metrics(args.service, args.limit)
        
        if args.endpoints:
            query_endpoints(args.service, args.limit)
        
        if args.traces:
            query_traces(args.service, args.limit)
        
        if args.latest_metrics:
            if not args.service:
                print("\n❌ 错误: --latest-metrics 需要配合 --service 使用")
                return
            query_latest_metrics(args.service, args.limit)
    
    print("\n" + "=" * 80)


if __name__ == '__main__':
    main()
